﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Common;

public class DatabaseWrapper
{
    private string _connectionstringkey, _cmdtext;
    private List<SqlParameter> _parms;
    private CommandType _cmdtype = CommandType.StoredProcedure;
    private List<Exception> _exceptions = new List<Exception>();

	public DatabaseWrapper(string connectionstringkey, string cmdtext)
	{
        if (string.IsNullOrWhiteSpace(connectionstringkey))
        {
            throw new ArgumentException("Connection string key cannot be empty.");
        }
        
        if (string.IsNullOrWhiteSpace(connectionstringkey))
        {
            throw new ArgumentException("Command text cannot be empty.");
        }

        _connectionstringkey = connectionstringkey;
        _cmdtext = cmdtext;                
	}

    public DatabaseWrapper(string connectionstringkey, string cmdtext, List<SqlParameter> parms)
    {
        if (string.IsNullOrWhiteSpace(connectionstringkey))
        {
            throw new ArgumentException("Connection string key cannot be empty.");
        }
        
        if (string.IsNullOrWhiteSpace(connectionstringkey))
        {
            throw new ArgumentException("Command text cannot be empty.");
        }

        _connectionstringkey = connectionstringkey;
        _cmdtext = cmdtext;
        _parms = parms;
    }

    public DatabaseWrapper(string connectionstringkey, string cmdtext, CommandType cmdtype)
    {
        if (string.IsNullOrWhiteSpace(connectionstringkey))
        {
            throw new ArgumentException("Connection string key cannot be empty.");
        }
        
        if (string.IsNullOrWhiteSpace(connectionstringkey))
        {
            throw new ArgumentException("Command text cannot be empty.");
        }

        _connectionstringkey = connectionstringkey;
        _cmdtext = cmdtext;        
        _cmdtype = cmdtype;
    }

    public DatabaseWrapper(string connectionstringkey, string cmdtext, List<SqlParameter> parms, CommandType cmdtype)
    {
        if (string.IsNullOrWhiteSpace(connectionstringkey)) 
        {
            throw new ArgumentException("Connection string key cannot be empty.");
        }
        
        if (string.IsNullOrWhiteSpace(connectionstringkey)) 
        {   
            throw new ArgumentException("Command text cannot be empty.");        
        }

        _connectionstringkey = connectionstringkey;
        _cmdtext = cmdtext;
        _parms = parms;
        _cmdtype = cmdtype;
    }

    private SqlConnection CreateConnection()
    {
        string connectionstring = ConfigurationManager.ConnectionStrings[_connectionstringkey].ConnectionString;

        if (string.IsNullOrWhiteSpace(connectionstring)) 
        {
            throw new ArgumentException("Connection string key is not in the web.config or the connection string is empty.");
        }
        
        return new SqlConnection(connectionstring);
    }

    private SqlCommand CreateCommand(SqlConnection connection)
    {
        return CreateCommand(connection, null);
    }

    private SqlCommand CreateCommand(SqlConnection connection, SqlTransaction transaction)
    {
        if (null == connection)
        {
            CreateConnection();
        }

        SqlCommand command = new SqlCommand(_cmdtext, connection);

        if (null != transaction)
        {
            command.Transaction = transaction;
        }

        command.CommandType = _cmdtype;

        if (null != _parms)
        {
            AddParameters(ref command);        
        }

        return command;
    }

    private void AddParameters(ref SqlCommand command)
    {
        if (null != command)
        {
            foreach (SqlParameter parm in _parms)
            {
                command.Parameters.Add(parm);
            }
        }
    }

    public void ClearParameters()
    {
        Parameters = null;
    }

    public DataTable ExecuteDataTable()
    {
        using (SqlConnection connection = CreateConnection())
        {
            using (SqlCommand command = CreateCommand(connection))
            {
                using (SqlDataAdapter dataadapter = new SqlDataAdapter(command))
                {
                    DataTable dt = new DataTable();
                    connection.Open();
                    dataadapter.Fill(dt);
                    return dt;
                }
            }
        }
    }
        
    public bool ExecuteNonQuery() 
    {
        try
        {
            using(SqlConnection connection = CreateConnection())
            {
                connection.Open();

                using(SqlTransaction transaction = connection.BeginTransaction())
                {
                    using(SqlCommand command = CreateCommand(connection, transaction))
                    {
                        command.ExecuteNonQuery();
                        transaction.Commit();
                        return true;
                    }
                }
            }
        }
        catch (Exception ex)
        {
            //no need to rollback the transaction
            //dispose rolls back the transaction if the commit isnt explicitly called
            //and dispose is called when the program leaves the transactions using block
            throw ex;
        }
    }
    
    public object ExecuteScalar()
    {
        using (SqlConnection connection = CreateConnection())
        {
            using (SqlCommand command = CreateCommand(connection))
            {
                connection.Open();
                return command.ExecuteScalar(); 
            }
        }
    }
    
    public object OutputParameterValue(string parameterName)
    {
        if (null != _parms && 0 < _parms.Count)
        {
            for (int i = 0; i < _parms.Count; i++)
            {
                if (_parms[i].ParameterName.ToUpper() == parameterName)
                {
                    return _parms[i].Value;
                }
            }
        }

        return null;
    }

    public List<SqlParameter> Parameters
    {
        get { return _parms; }
        set { _parms = value; }
    }

    public string CommandText
    {
        get { return _cmdtext; }
        set { _cmdtext = value; }
    }

    public List<Exception> Exceptions
    {
        get { return _exceptions; }
        set { _exceptions = value; }
    }
}